package com.h819.core.file.excel.jxl;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;
import org.apache.log4j.Logger;

import java.io.File;
import java.io.IOException;

//经过测试发现，在创建  WritableCellFormat 变量的时候，如果指定了字体，如
//WritableCellFormat fontFormat = new WritableCellFormat(font);
//就会不定期的出现 java.lang.ArrayIndexOutOfBoundsException: 5 错误
// ====
//所以在创建 WritableCellFormat 的时候，不能指定 font 参数，用默认的构造方法
//WritableCellFormat fontFormat = new WritableCellFormat();
//且不指定字体
//如果应用下面的方法，也会出错
//fontFormat.setFont(font);
/**
 * @ClassName: JXLColourExample
 * @Description: TODO(该方法测试了 jxl.format.Colour 类的所有颜色，便于直观查看 (jxl 2.6))
 * @author h819
 * @date Jul 29, 2009 12:34:00 AM
 * @since jxl 2.6
 * @version V1.0
 */
public class JXLColourExample {

	private Logger log = Logger.getLogger(JXLColourExample.class);

	// 设置文字格式
	 public WritableFont defaultFont = new WritableFont(
	 WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
	 UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);

	public jxl.format.Alignment defaultHorizontal = jxl.format.Alignment.CENTRE;
	public jxl.format.VerticalAlignment defaultVertical = jxl.format.VerticalAlignment.CENTRE;

	// 把颜色编号，便于查看每种颜色的样式
	// jxl 2.6 中描述的颜色值
	private Colour c1 = Colour.AQUA;
	private Colour c2 = Colour.AUTOMATIC;
	private Colour c3 = Colour.BLACK;
	private Colour c4 = Colour.BLUE;
	private Colour c5 = Colour.BLUE_GREY;
	private Colour c6 = Colour.BLUE2;
	private Colour c7 = Colour.BRIGHT_GREEN;
	private Colour c8 = Colour.BROWN;
	private Colour c9 = Colour.CORAL;
	private Colour c10 = Colour.DARK_BLUE;
	private Colour c11 = Colour.DARK_BLUE2;
	private Colour c12 = Colour.DARK_GREEN;
	private Colour c13 = Colour.DARK_PURPLE;
	private Colour c14 = Colour.DARK_RED;
	private Colour c15 = Colour.DARK_RED2;
	private Colour c16 = Colour.DARK_TEAL;
	private Colour c17 = Colour.DARK_YELLOW;
	private Colour c18 = Colour.DEFAULT_BACKGROUND;
	private Colour c19 = Colour.DEFAULT_BACKGROUND1;
	private Colour c20 = Colour.GOLD;
	private Colour c21 = Colour.GRAY_25;
	private Colour c22 = Colour.GRAY_50;
	private Colour c23 = Colour.GRAY_80;
	private Colour c24 = Colour.GREEN;
	private Colour c25 = Colour.GREY_25_PERCENT;
	private Colour c26 = Colour.GREY_40_PERCENT;
	private Colour c27 = Colour.GREY_50_PERCENT;
	private Colour c28 = Colour.GREY_80_PERCENT;
	private Colour c29 = Colour.ICE_BLUE;
	private Colour c30 = Colour.INDIGO;
	private Colour c31 = Colour.IVORY;
	private Colour c32 = Colour.LAVENDER;
	private Colour c33 = Colour.LIGHT_BLUE;
	private Colour c34 = Colour.LIGHT_GREEN;
	private Colour c35 = Colour.LIGHT_ORANGE;
	private Colour c36 = Colour.LIGHT_TURQUOISE;
	private Colour c37 = Colour.LIGHT_TURQUOISE2;
	private Colour c38 = Colour.LIME;
	private Colour c39 = Colour.OCEAN_BLUE;
	private Colour c40 = Colour.OLIVE_GREEN;
	private Colour c41 = Colour.ORANGE;
	private Colour c42 = Colour.PALE_BLUE;
	private Colour c43 = Colour.PALETTE_BLACK;
	private Colour c44 = Colour.PERIWINKLE;
	private Colour c45 = Colour.PINK;
	private Colour c46 = Colour.PINK2;
	private Colour c47 = Colour.PLUM;
	private Colour c48 = Colour.PLUM2;
	private Colour c49 = Colour.RED;
	private Colour c50 = Colour.ROSE;
	private Colour c51 = Colour.SEA_GREEN;
	private Colour c52 = Colour.SKY_BLUE;
	private Colour c53 = Colour.TAN;
	private Colour c54 = Colour.TEAL;
	private Colour c55 = Colour.TEAL2;
	private Colour c56 = Colour.TURQOISE2;
	private Colour c57 = Colour.TURQUOISE;
	private Colour c58 = Colour.UNKNOWN;
	private Colour c59 = Colour.VERY_LIGHT_YELLOW;
	private Colour c60 = Colour.VIOLET;
	private Colour c61 = Colour.VIOLET2;
	private Colour c62 = Colour.WHITE;
	private Colour c63 = Colour.YELLOW;
	private Colour c64 = Colour.YELLOW2;

	// index 从 1 开始引用
	private Colour c[] = new Colour[] { null, c1, c2, c3, c4, c5, c6, c7, c8,
			c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21,
			c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32, c33, c34,
			c35, c36, c37, c38, c39, c40, c41, c42, c43, c44, c45, c46, c47,
			c48, c49, c50, c51, c52, c53, c54, c55, c56, c57, c58, c59, c60,
			c61, c62, c63, c64 };

	/**
	 * 得到白色(c62)背景，水平居中，垂直居中，默认字体的字符型 WritableCellFormat
	 * 
	 * @return
	 */
	public WritableCellFormat getTXTWritableCellFormat() {

		WritableCellFormat fontFormat = new WritableCellFormat();
		try {
			// 默认白色背景
			fontFormat.setBackground(c[62]);
			// 水平对齐方式: 居中
			fontFormat.setAlignment(defaultHorizontal);
			// 垂直对齐方式：居中
			fontFormat.setVerticalAlignment(defaultVertical);
			// 边框
			fontFormat.setBorder(jxl.format.Border.ALL,
					jxl.format.BorderLineStyle.THIN);
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return fontFormat;
	}

	/**
	 * 得到默认对齐方式和字体，指定颜色的字符型 WritableCellFormat
	 * 
	 * @param backGroundColourNumber
	 *            颜色索引位置，具体颜色样式，执行 colourExampleText() 函数查看一下
	 * @return
	 */

	public WritableCellFormat getTXTWritableCellFormat(
			int backGroundColourNumber) {

		WritableCellFormat fontFormat = new WritableCellFormat();
		try {
			fontFormat.setBackground(c[backGroundColourNumber]);
			// 水平对齐方式: 居中
			fontFormat.setAlignment(defaultHorizontal);
			// 垂直对齐方式：居中
			fontFormat.setVerticalAlignment(defaultVertical);

			fontFormat.setBorder(jxl.format.Border.ALL,
					jxl.format.BorderLineStyle.THIN);
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return fontFormat;
	}

	/**
	 * !!! 由于构造方法带有 WritableFont 参数，出现前面提到的问题
	 * 得到默认对齐方式，指定字体，指定颜色的字符型 WritableCellFormat
	 * 
	 * @param backGroundColourNumber
	 *            颜色索引位置，具体颜色样式，执行 colourExampleText() 函数查看一下
	 * @param font
	 *            字体
	 * @return
	 */
//	public WritableCellFormat getTXTWritableCellFormat(
//			int backGroundColourNumber, WritableFont font) {
//		WritableCellFormat fontFormat = new WritableCellFormat(font);
//		try {
//			fontFormat.setBackground(c[backGroundColourNumber]);
//			// 水平对齐方式: 居中
//			fontFormat.setAlignment(defaultHorizontal);
//			// 垂直对齐方式：居中
//			fontFormat.setVerticalAlignment(defaultVertical);
//
//			fontFormat.setBorder(jxl.format.Border.ALL,
//					jxl.format.BorderLineStyle.THIN);
//		} catch (WriteException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		return fontFormat;
//	}

	/**
	 * 得到指定对齐方式，指定字体，指定颜色的字符型 WritableCellFormat
	 * 
	 * @param backGroundColourNumber
	 *            颜色索引位置，具体颜色样式，执行 colourExampleText() 函数查看一下
	 * @param horizontal
	 *            水平对齐方式
	 * @param vertical
	 *            垂直对齐方式
	 * @param font
	 *            字体
	 * @return
	 */
	public WritableCellFormat getTXTWritableCellFormat(
			int backGroundColourNumber, Alignment horizontal,
			VerticalAlignment vertical) {
		WritableCellFormat fontFormat = new WritableCellFormat();
		try {
			fontFormat.setBackground(c[backGroundColourNumber]);
			// 水平对齐方式
			fontFormat.setAlignment(horizontal);
			// 垂直对齐方式
			fontFormat.setVerticalAlignment(vertical);

			fontFormat.setBorder(jxl.format.Border.ALL,
					jxl.format.BorderLineStyle.THIN);
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return fontFormat;

	}

	/**
	 * 得到白色背景，水平居中，垂直居中，默认字体的数字型 WritableCellFormat
	 * 
	 * @param format
	 *            数字类型，可以查看 jxl.write.NumberFormats 类
	 * @return
	 */

	public WritableCellFormat getNumberWritableCellFormat(
			jxl.biff.DisplayFormat format) {

		WritableCellFormat fontFormat = new WritableCellFormat(format);

		try {
			// 默认白色背景
			fontFormat.setBackground(c[62]);
			// 水平对齐方式: 居中
			fontFormat.setAlignment(defaultHorizontal);
			// 垂直对齐方式：居中
			fontFormat.setVerticalAlignment(defaultVertical);

			fontFormat.setBorder(jxl.format.Border.ALL,
					jxl.format.BorderLineStyle.THIN);
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return fontFormat;
	}

	/**
	 * 得到指定背景，默认水平居中，默认垂直居中，默认字体的数字型 WritableCellFormat
	 * 
	 * @param backGroundColourNumber
	 *            颜色索引位置，具体颜色样式，执行 colourExampleText() 函数查看一下
	 * @param format
	 *            数字类型，可以查看 jxl.write.NumberFormats 类
	 * @return
	 */
	public WritableCellFormat getNumberWritableCellFormat(
			int backGroundColourNumber, jxl.biff.DisplayFormat format) {

		WritableCellFormat fontFormat = new WritableCellFormat(format);
		try {
			// 默认字体
			// fontFormat.setFont(defaultFont);
			// 默认白色背景
			fontFormat.setBackground(c[backGroundColourNumber]);
			// 水平对齐方式: 居中
			fontFormat.setAlignment(defaultHorizontal);
			// 垂直对齐方式：居中
			fontFormat.setVerticalAlignment(defaultVertical);

			fontFormat.setBorder(jxl.format.Border.ALL,
					jxl.format.BorderLineStyle.THIN);
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return fontFormat;
	}

	/**
	 * 该方法测试了 jxl.format.Colour 类的所有颜色，便于直观查看 (jxl 2.6)
	 * 
	 * @param file 生产的颜色样本，excel 行号代表了颜色代号
	 */
	public void colourExampleText(File file) {

		WritableWorkbook wwb = null;

		// 建立文件
		try {
			// 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
			wwb = Workbook.createWorkbook(file);
		} catch (IOException e) {
			System.out.println("exception 0.");
			ExcelUtils.closeWritableWorkbook(wwb);
			e.printStackTrace();

		}

		if (wwb == null)
			return;

		// 开始操作excel表
		// 创建一个可写入的工作表
		// Workbook的createSheet方法有两个参数，第一个是工作表的名称，第二个是工作表在工作薄中的位置
		WritableSheet ws = wwb.createSheet("sheet1", 0);

		try {
			for (int i = 1; i < c.length; i++) {
				// log.info("i :" +i);
				Label labelTemp = new Label(0, i - 1, "text",
						getTXTWritableCellFormat(i));
				ws.addCell(labelTemp);
			}

		} catch (RowsExceededException e) {
			System.out.println("exception 2.");
			e.printStackTrace();
			// 发生异常关闭
			ExcelUtils.closeWritableWorkbook(wwb);
		} catch (WriteException e) {
			System.out.println("exception 3.");
			e.printStackTrace();
			// 发生异常关闭
			ExcelUtils.closeWritableWorkbook(wwb);
		}

		// 从内存中写入文件中
		try {
			wwb.write();
			wwb.close();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			System.out.println("exception 4.");
			e.printStackTrace();
		} catch (IOException e) {
			System.out.println("exception 5.");
			e.printStackTrace();
		}
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		JXLColourExample c = new JXLColourExample();

		// 颜色测试
		c.colourExampleText(new File("d:\\jxlColourExample.xls"));

	}

}
